<?php
defined('IN_MAIN') or exit('Invalid Access!');

function calc_pspgs($in_date){
/*
This function calculates the personal sales and group sales of all the members for the month of input in_date
for all the hierarchy (sponsor, placement, plan b) and store the result to the database.
*/

	global $vars;
	$db=$vars["db"];
	$comm_time = strtotime($in_date);
	$month = date('n', $comm_time);
	$year = date('Y', $comm_time);
	$calc_date['start'] = date('Y-m-d H:i:s', mktime(0,0,0,$month,1,$year));
	$calc_date['end'] = date('Y-m-d H:i:s', mktime(23,59,59,$month+1,0,$year));
	$comm_title = "Monthly Personal Group Sales Calculation";
	
	//setting up - transfer activation into calc table, for faster performance
	$sale_safe = false;
	if(!mysql_query("truncate $db->purchase_calc")){
		$sale_safe = true;
	}else{
		$ins_total = 0;
		$t = @mysql_num_rows($r=mysql_query($s="select uid, sum(total_bv) as bv, sum(total_dp) as dp, sum(total_rp) as rp from $db->purchase where status='confirmed' and member_sale='y' and first_sale='y' and no_bv!='y' and cdate>='$calc_date[start]' and cdate<='$calc_date[end]' group by uid"));
		for($i=0;$i<$t;$i++){
			$his = mysql_fetch_assoc($r);
			$ins .= "('$his[uid]', '$his[bv]', '$his[dp]', '$his[rp]'),";
			$ins_total++;
			if($ins_total > 200){
				$ins = rtrim($ins, ',');
				$ins_sql = "insert into $db->purchase_calc (uid, total_bv, total_dp, total_rp) values $ins";
				if(!mysql_query($ins_sql)){
					$i = $t;
					$sale_safe = true;//abort, use slower way
				}
				$ins = '';
				$ins_total = 0;
			}
		}
		if($ins){
			$ins = rtrim($ins, ',');
			$ins_sql = "insert into $db->purchase_calc (uid, total_bv, total_dp, total_rp) values $ins";
			if(!mysql_query($ins_sql)){
				$sale_safe = true;//abort, use slower way
			}
		}
		unset($his, $ins, $ins_sql, $ins_total);
	}
	//-----
	
	$genealogy = array('matrix_upline');
	foreach($genealogy as $which_network){
		//get all the members that is not terminated
		$t = @mysql_num_rows($r=mysql_query("select * from $db->users where status!='terminated' order by cdate desc"));
		for($i=0;$i<$t;$i++){
			$user = mysql_fetch_assoc($r);
			$uid = $user['id'];
			$this_uid_pgs['bv'] = $this_uid_pgs['dp'] = $this_uid_pgs['rp'] = 0;
			//downlines = get 1st level downline of uid
			$t2 = @mysql_num_rows($r2=mysql_query("select id, $which_network from $db->users where $which_network='$uid'"));
			for($j=0;$j<$t2;$j++){
				$downline = mysql_fetch_assoc($r2);
				$did = $downline['id'];
				$all_did = $did;
				$uplines[0] = $did;
				$this_pgs['bv'] = $this_pgs['dp'] = $this_pgs['rp'] = 0;
				//if did already calculated the group sales
				if($pspgs[$did]){
					$total_pgs['bv'] = $pspgs[$did]['pgs']['bv'];
					$total_pgs['dp'] = $pspgs[$did]['pgs']['dp'];
					$total_pgs['rp'] = $pspgs[$did]['pgs']['rp'];
				}else{
					do{
						//ddownlines = get 1st level downlines of uplines
						$t3 = @mysql_num_rows($r3=mysql_query("select id from $db->users where $which_network in (".implode(',', $uplines).")"));
						unset ($uplines);
						for($k=0;$k<$t3;$k++){
							$ddownlines = mysql_fetch_assoc($r3);
							$ddid = $ddownlines['id'];
							//if ddid already calculated the group sales
							if($pspgs[$ddid]){
								$this_pgs['bv'] += $pspgs[$ddid]['pgs']['bv'];
								$this_pgs['dp'] += $pspgs[$ddid]['pgs']['dp'];
								$this_pgs['rp'] += $pspgs[$ddid]['pgs']['rp'];
							}else{
								$uplines[] = $ddid;
								$all_did .= ','.$ddid;
							}
						}
					}while ($uplines);
					
					//total_pgs = get total pgs for all_did + this_pgs
					if($sale_safe){
						$sql = "select sum(total_bv) as bv, sum(total_dp) as dp, sum(total_rp) as rp from $db->purchase where uid in ($all_did) and status='confirmed' and first_sale='y' and no_bv!='y' and cdate>='$calc_date[start]' and cdate<='$calc_date[end]'";
					}else{
						$sql = "select sum(total_bv) as bv, sum(total_dp) as dp, sum(total_rp) as rp from $db->purchase_calc where uid in ($all_did)";
					}
					$total_pgs['bv'] = @mysql_result(mysql_query($sql), 0, 'bv') or 0;
					$total_pgs['dp'] = @mysql_result(mysql_query($sql), 0, 'dp') or 0;
					$total_pgs['rp'] = @mysql_result(mysql_query($sql), 0, 'rp') or 0;
					$total_pgs['bv'] += $this_pgs['bv'];
					$total_pgs['dp'] += $this_pgs['dp'];
					$total_pgs['rp'] += $this_pgs['rp'];
				}
				$this_uid_pgs['bv'] += $total_pgs['bv'];
				$this_uid_pgs['dp'] += $total_pgs['dp'];
				$this_uid_pgs['rp'] += $total_pgs['rp'];
			}
			
			//personal sales
			if($sale_safe){
				$sql = "select sum(total_bv) as bv, sum(total_dp) as dp, sum(total_rp) as rp from $db->purchase where uid in ($uid) and status='confirmed' and first_sale='y' and no_bv!='y' and cdate>='$calc_date[start]' and cdate<='$calc_date[end]'";
			}else{
				$sql = "select sum(total_bv) as bv, sum(total_dp) as dp, sum(total_rp) as rp from $db->purchase_calc where uid in ($uid)";
			}
			$ps['bv'] = @mysql_result(mysql_query($sql), 0, 'bv') or 0;
			$ps['dp'] = @mysql_result(mysql_query($sql), 0, 'dp') or 0;
			$ps['rp'] = @mysql_result(mysql_query($sql), 0, 'rp') or 0;
			$pgs['bv'] = $this_uid_pgs['bv'] + $ps['bv'];
			$pgs['dp'] = $this_uid_pgs['dp'] + $ps['dp'];
			$pgs['rp'] = $this_uid_pgs['rp'] + $ps['rp'];
			$pspgs[$uid]['ps']['bv'] = $ps['bv'];
			$pspgs[$uid]['ps']['dp'] = $ps['dp'];
			$pspgs[$uid]['ps']['rp'] = $ps['rp'];
			$pspgs[$uid]['pgs']['bv'] = $pgs['bv'];
			$pspgs[$uid]['pgs']['dp'] = $pgs['dp'];
			$pspgs[$uid]['pgs']['rp'] = $pgs['rp'];
			
			//record to database
			$sql = "insert into $db->purchase_fig (uid, network, ps_bv, ps_dp, ps_rp, pgs_bv, pgs_dp, pgs_rp, year, month) values ('$uid', '$which_network', '$ps[bv]', '$ps[dp]', '$ps[rp]', '$pgs[bv]', '$pgs[dp]', '$pgs[rp]', '$year', '$month')";
			if(!mysql_query($sql)){
				$insert_err .= "$sql\n";
			}
		}
		unset($pspgs);
	}
	
	if($insert_err){
		$errmsg = ($insert_err? "$comm_title calculation error:\n\n$insert_err\n" : "");
		$status = false;
	}else{
		$status = true;
	}
	$result['status'] = $status;
	$result['errmsg'] = $errmsg;
	
	return $result;
}
?>